Option Explicit On
'Option Strict On

Imports System
Imports System.Collections
Imports System.Collections.Generic
Imports System.Runtime.Serialization
Imports System.Runtime.Serialization.Formatters.Binary
Imports System.IO
Imports System.ComponentModel
Imports IronPython.Hosting
Imports IronPython.Compiler
Imports System.Text.RegularExpressions

Namespace C3I.CodegenTools

#Region "Public Class LayoutTemplate"
    <Serializable()> Public Class LayoutTemplate
        Private _PrefixTemplate As String = String.Empty, _PostfixTemplate As String = "", _LayoutName As String = ""
        Private _LayoutFileName As String = String.Empty, _IsDirty As Boolean

        'prefix template file
        Public Sections As New Generic.List(Of SectionTemplate)

        Public Property IsDirty() As Boolean
            Get
                Return _IsDirty
            End Get

            Set(ByVal Value As Boolean)
                _IsDirty = Value
            End Set
        End Property

        Public Property PrefixTemplate() As String
            Get
                Return _PrefixTemplate
            End Get

            Set(ByVal Value As String)
                _PrefixTemplate = Value
            End Set
        End Property

        Public Property LayoutName() As String
            Get
                Return _LayoutName
            End Get

            Set(ByVal Value As String)
                _LayoutName = Value
            End Set
        End Property


        Public Property PostFixTemplate() As String
            Get
                Return _PostfixTemplate
            End Get

            Set(ByVal Value As String)
                _PostfixTemplate = Value
            End Set
        End Property

        Public Property FileName() As String
            Get
                Return _LayoutFileName
            End Get

            Set(ByVal Value As String)
                _LayoutFileName = Value
            End Set
        End Property

        Public Shared Function LoadLayout(ByVal strFileName As String) As LayoutTemplate
            Dim f As New BinaryFormatter
            Dim s As FileStream = New FileStream(strFileName, FileMode.Open)
            Dim layoutTemplate As LayoutTemplate

            layoutTemplate = CType(f.Deserialize(s), LayoutTemplate)
            s.close()
            Return layoutTemplate


        End Function

        Public Sub New()
            _LayoutName = "New Layout Template"

        End Sub

    End Class
#End Region

#Region "Public Class SectionTemplate"
    <Serializable()> Public Class SectionTemplate
        Private _PrefixTemplate As String = "", _PostfixTemplate As String = "", _Description As String = ""
        Private _ProcessFields As Boolean, _SectionDetail As String = ""
        Dim _conditionArray As New BindingList(Of Condition)

        Public Property Conditions() As BindingList(Of Condition)
            Get
                Return _conditionArray
            End Get

            Set(ByVal Value As BindingList(Of Condition))
                _conditionArray = Value
            End Set
        End Property

        Public DataTypes As New Generic.List(Of DataType)
        Public Property PrefixTemplate() As String
            Get
                Return _PrefixTemplate
            End Get

            Set(ByVal Value As String)
                _PrefixTemplate = Value
            End Set
        End Property
        Public Property Detail() As String
            Get
                Return _SectionDetail
            End Get

            Set(ByVal Value As String)
                _SectionDetail = Value
            End Set
        End Property

        Public Property PostFixTemplate() As String
            Get
                Return _PostfixTemplate
            End Get

            Set(ByVal Value As String)
                _PostfixTemplate = Value
            End Set
        End Property

        Public Property Description() As String
            Get
                Return _Description
            End Get

            Set(ByVal Value As String)
                _Description = Value
            End Set
        End Property
    End Class


#End Region
    <Serializable()> Public Class Index
        Private _Name As String, _Description As String, _IsUnique As Boolean
        Private _Columns As String

        Public Property Name() As String
            Get
                Return _Name
            End Get

            Set(ByVal Value As String)
                _Name = Value
            End Set
        End Property

        Public Property Description() As String
            Get
                Return _Description
            End Get

            Set(ByVal Value As String)
                _Description = Value
            End Set
        End Property

        Public Property IsUnique() As Boolean
            Get
                Return _IsUnique
            End Get

            Set(ByVal Value As Boolean)
                _IsUnique = Value
            End Set
        End Property

        Public Property Column() As String
            Get
                Return _Columns
            End Get

            Set(ByVal Value As String)
                _Columns = Value
            End Set
        End Property
    End Class

    Public Class DBObject
        Private _Name As String, _Description As String
        Private _Type As String, _Schema As String

        Public Property Name() As String
            Get
                Return _Name
            End Get

            Set(ByVal Value As String)
                _Name = Value
            End Set
        End Property

        Public Property Schema() As String
            Get
                Return _Schema
            End Get

            Set(ByVal Value As String)
                _Schema = Value
            End Set
        End Property


        Public Property Description() As String
            Get
                Return _Description
            End Get

            Set(ByVal Value As String)
                _Description = Value
            End Set
        End Property

        Public Property Type() As String
            Get
                Return _Type
            End Get

            Set(ByVal Value As String)
                _Type = Value
            End Set
        End Property

    End Class

#Region "Public Class DataType"
    <Serializable()> Public Class DataType
        Dim _Type As String, _TypeDescription As String, _Template As String
        Dim _Description As String, _FieldType As String
        Dim _isKey As Boolean, _isForeignKey As Boolean
        Dim _FieldName As String, _FieldValue As String, _Level As Int16, _PropertyOperator As String
        Dim _nextStep As Int16, _conditionArray As New BindingList(Of Condition)
        Dim _EmptyField As Boolean, _OrField As Boolean

        Public Property Conditions() As BindingList(Of Condition)
            Get
                Return _conditionArray
            End Get

            Set(ByVal Value As BindingList(Of Condition))
                _conditionArray = Value
            End Set
        End Property


        Public Property NextStep() As Int16
            Get
                Return _nextStep
            End Get

            Set(ByVal Value As Int16)
                _nextStep = Value
            End Set
        End Property
        Public Property OrField() As Boolean
            Get
                Return _OrField
            End Get

            Set(ByVal Value As Boolean)
                _OrField = Value
            End Set
        End Property

        Public Property EmptyField() As Boolean
            Get
                Return _EmptyField
            End Get

            Set(ByVal Value As Boolean)
                _EmptyField = Value
            End Set
        End Property

        Public Property IsKey() As Boolean
            Get
                Return _isKey
            End Get

            Set(ByVal Value As Boolean)
                _isKey = Value
            End Set
        End Property

        Public Property IsForeignKey() As Boolean
            Get
                Return _isForeignKey
            End Get

            Set(ByVal Value As Boolean)
                _isForeignKey = Value
            End Set
        End Property

        Public Property PropertyOperator() As String
            Get
                Return _PropertyOperator
            End Get

            Set(ByVal Value As String)
                _PropertyOperator = Value
            End Set
        End Property

        Public Property Level() As Int16
            Get
                Return _Level
            End Get

            Set(ByVal Value As Int16)
                _Level = Value
            End Set
        End Property

        Public Property FieldType() As String
            Get
                Return _FieldType
            End Get

            Set(ByVal Value As String)
                _FieldType = Value
            End Set
        End Property


        Public Property Type() As String
            Get
                Return _Type
            End Get

            Set(ByVal Value As String)
                _Type = Value
            End Set
        End Property

        Public Property Template() As String
            Get
                Return _Template
            End Get

            Set(ByVal Value As String)
                _Template = Value
            End Set
        End Property

        Public Property Description() As String
            Get
                Return _Description
            End Get

            Set(ByVal Value As String)
                _Description = Value
            End Set
        End Property

        Public Property FieldName() As String
            Get
                Return _FieldName
            End Get

            Set(ByVal Value As String)
                _FieldName = Value
            End Set
        End Property

        Public Property FieldValue() As String
            Get
                Return _FieldValue
            End Get

            Set(ByVal Value As String)
                _FieldValue = Value
            End Set
        End Property
        Function Clone() As DataType
            Dim dt As New DataType

            dt.Description = Me.Description
            dt.FieldName = Me.FieldName
            dt.FieldType = Me.FieldType
            dt.FieldValue = Me.FieldValue
            dt.IsForeignKey = Me.IsForeignKey
            dt.IsKey = Me.IsKey
            dt.Level = Me.Level
            dt.PropertyOperator = Me.PropertyOperator
            dt.Template = Me.Template
            dt.Type = Me.Type
            dt.Level = Me.Level
            dt.OrField = Me.OrField
            dt.EmptyField = Me.EmptyField

            For Each con As Condition In Me.Conditions
                Dim newCondition As New Condition
                newCondition.CompOperator = con.CompOperator
                newCondition.CompProperty = con.CompProperty
                newCondition.CompValue = con.CompValue
                dt.Conditions.Add(newCondition)
            Next
            Return dt

        End Function
        Public Sub New()
            Me.Level = 1
        End Sub

    End Class
#End Region

#Region "Public Class PropertyValue"
    <Serializable()> Public Class PropertyValue

        Dim _Property As String, _Value As String, _System As Boolean

        Public Property Value() As String
            Get
                Return _Value
            End Get

            Set(ByVal Value As String)
                _Value = Value
            End Set
        End Property


        Public Property Name() As String
            Get
                Return _Property
            End Get

            Set(ByVal Value As String)
                _Property = Value
            End Set
        End Property

        Public Property System() As Boolean
            Get
                Return _System
            End Get

            Set(ByVal Value As Boolean)
                _System = Value
            End Set
        End Property

        Public Sub New(ByVal Value As String, ByVal SystemFlag As Boolean)
            _Value = Value
            _System = SystemFlag
        End Sub

    End Class
#End Region

#Region "Public Class DatabaseServer"
    <Serializable()> Public Class DatabaseServer
        Public Databases As New Generic.SortedDictionary(Of String, DatabaseStructure)
        Private _ConnectionString As String, _Username As String, _Password As String
        Private _Description As String, _NTAuthentication As Boolean
        Private _Name As String, _FileName As String, _IsDirty As Boolean, _Port As String
        Private _DatabaseType As String, _propertyValues As New BindingList(Of PropertyValue)


        Public Property FileName() As String
            Get
                Return _FileName
            End Get

            Set(ByVal Value As String)
                _FileName = Value
            End Set
        End Property

        Public Property UserName() As String
            Get
                Return _Username
            End Get

            Set(ByVal Value As String)
                _Username = Value
            End Set
        End Property

        Public Property DatabaseType() As String
            Get
                Return _DatabaseType
            End Get

            Set(ByVal Value As String)
                _DatabaseType = Value
            End Set
        End Property


        Public Property Password() As String
            Get
                Return _Password
            End Get

            Set(ByVal Value As String)
                _Password = Value
            End Set
        End Property

        Public Property Port() As String
            Get
                Return _Port
            End Get

            Set(ByVal Value As String)
                _Port = Value
            End Set
        End Property


        Public Property IsDirty() As Boolean
            Get
                Return _IsDirty
            End Get

            Set(ByVal Value As Boolean)
                _IsDirty = Value
            End Set
        End Property

        Public Property ConnectionString() As String
            Get
                Return _ConnectionString
            End Get

            Set(ByVal Value As String)
                _ConnectionString = Value
            End Set
        End Property

        Public Property Name() As String
            Get
                Return _Name
            End Get

            Set(ByVal Value As String)
                _Name = Value
            End Set
        End Property

        Public Property UseNTAuthentication() As Boolean
            Get
                Return _NTAuthentication
            End Get

            Set(ByVal Value As Boolean)
                _NTAuthentication = Value
            End Set
        End Property


        Public Sub New()
            _Name = "New database"
            _ConnectionString = ""
            _DatabaseType = "SQL Server"
            _IsDirty = False


        End Sub

    End Class

#End Region

#Region "Public Class DatabaseStructure"
    <Serializable()> Public Class DatabaseStructure
        Public TableLayouts As New Generic.SortedDictionary(Of String, TableLayout)
        Private _ConnectionString As String, _Server As String = "", _dataSourceName As String = ""
        Private _Description As String, _bUseTrustedConnection As Boolean = False
        Private _Name As String, _Port As String
        Public Values As New BindingList(Of PropertyValue), Indexes As Dictionary(Of String, Index)


        Public Property UseTrustedConnection() As Boolean
            Get
                Return _bUseTrustedConnection
            End Get

            Set(ByVal Value As Boolean)
                _bUseTrustedConnection = Value
            End Set
        End Property

        Public Property Port() As String
            Get
                Return _Port
            End Get

            Set(ByVal Value As String)
                _Port = Value
            End Set
        End Property


        Public Property DataSourceName() As String
            Get
                Return _dataSourceName
            End Get

            Set(ByVal Value As String)
                _dataSourceName = Value
            End Set
        End Property

        Public Property Server() As String
            Get
                Return _Server
            End Get

            Set(ByVal Value As String)
                _Server = Value
            End Set
        End Property

        Public Property ConnectionString() As String
            Get
                Return _ConnectionString
            End Get

            Set(ByVal Value As String)
                _ConnectionString = Value
            End Set
        End Property

        Public Property Name() As String
            Get
                Return _Name
            End Get

            Set(ByVal Value As String)
                _Name = Value
            End Set
        End Property
    End Class

#End Region

#Region "Public Class TableLayout"

    <Serializable()> Public Class TableLayout
        Public Columns As New Generic.List(Of Column)
        Private _TableName As String, _Description As String, _AltName As String, _AltName2 As String
        Private _Properties As New Dictionary(Of String, PropertyValue), _SelectSQL As String
        Protected _foreignTables As New List(Of String()), Indexes As Dictionary(Of String, Index)

        Public Sub New()
            _Properties.Add("TableFlag1", New PropertyValue("False", True))

            _Properties.Add("TableFlag2", New PropertyValue("False", True))
            _Properties.Add("TableFlag3", New PropertyValue("False", True))
            _Properties.Add("TableFlag4", New PropertyValue("False", True))
            _Properties.Add("TableFlag5", New PropertyValue("False", True))
            _Properties.Add("TableFlag6", New PropertyValue("False", True))
            _Properties.Add("TableFlag7", New PropertyValue("False", True))
            _Properties.Add("TableFlag8", New PropertyValue("False", True))
            _Properties.Add("TableFlag9", New PropertyValue("False", True))
            _Properties.Add("TableFlag10", New PropertyValue("False", True))
            _Properties.Add("TableFlag11", New PropertyValue("False", True))
            _Properties.Add("TableFlag12", New PropertyValue("False", True))
            _Properties.Add("SQLFromStatement", New PropertyValue("", True))
            _Properties.Add("SQLSelectFields", New PropertyValue("", True))
            _Properties.Add("TableComment1", New PropertyValue("", True))

            _Properties.Add("TableMessage1", New PropertyValue("", True))
            _Properties.Add("TableMessage2", New PropertyValue("", True))
            _Properties.Add("TableMessage3", New PropertyValue("", True))
            _Properties.Add("TableMessage4", New PropertyValue("", True))
            _Properties.Add("TableMessage5", New PropertyValue("", True))

            _Properties.Add("IgnoreTable", New PropertyValue("False", True))
            _Properties.Add("IsView", New PropertyValue("False", True))
            _Properties.Add("IsSP", New PropertyValue("False", True))

            _Properties.Add("TableAltName", New PropertyValue("", True))
            _Properties.Add("TableAltNameB", New PropertyValue("", True))
            _Properties.Add("SelectSQL", New PropertyValue("", True))
            _Properties.Add("TableSchema", New PropertyValue("", True))
            _Properties.Add("IsDirty", New PropertyValue("False", True))

        End Sub

        Public ReadOnly Property Properties() As Dictionary(Of String, PropertyValue)
            Get
                Return _Properties
            End Get

        End Property
        Public Property ForeignTables() As List(Of String())
            Get
                Return _foreignTables
            End Get

            Set(ByVal Value As List(Of String()))
                _foreignTables = Value
            End Set
        End Property


        Public Property Name() As String
            Get
                Return _TableName
            End Get

            Set(ByVal Value As String)
                _TableName = Value
            End Set
        End Property

        Public Property Description() As String
            Get
                Return _Description
            End Get

            Set(ByVal Value As String)
                _Description = Value

            End Set
        End Property
    End Class
#End Region

#Region "Public Class Column"
    <Serializable()> Public Class Column
        'default flag
        'datatype
        'template file
        Dim _Type As String, _ColumnName As String
        Public Values As New Dictionary(Of String, PropertyValue)

        Dim _isKey As Boolean, _ignoreField As Boolean, _isForeignKey As Boolean
        Dim _NativeFieldType As String, _DataType As String, _isVirtualField As Boolean

        Public Property Ignore() As Boolean
            Get
                Return _ignoreField
            End Get

            Set(ByVal Value As Boolean)
                _ignoreField = Value
            End Set
        End Property

        Public Property IsVirtualField() As Boolean
            Get
                Return _isVirtualField
            End Get

            Set(ByVal Value As Boolean)
                _isVirtualField = Value
            End Set
        End Property


        'Date/String/Integer/Double
        Public Property BaseDataType() As String
            Get
                Return _DataType
            End Get

            Set(ByVal Value As String)
                _DataType = Value
            End Set
        End Property


        Public Property NativeFieldType() As String
            Get
                Return _NativeFieldType
            End Get

            Set(ByVal Value As String)
                _NativeFieldType = Value
            End Set
        End Property

        'generic type
        Public Property GenericType() As String
            Get
                Return _Type
            End Get

            Set(ByVal Value As String)
                _Type = Value
            End Set
        End Property

        Public Property Name() As String
            Get
                Return _ColumnName
            End Get

            Set(ByVal Value As String)
                _ColumnName = Value
            End Set
        End Property

        Public Property IsKey() As Boolean
            Get
                Return _isKey
            End Get

            Set(ByVal Value As Boolean)
                _isKey = Value
            End Set
        End Property

        Public Property IsForeignKey() As Boolean
            Get
                Return _isForeignKey
            End Get

            Set(ByVal Value As Boolean)
                _isForeignKey = Value
            End Set
        End Property

        Function Clone() As Column
            Dim newColumn As New Column
            newColumn = CType(Me.MemberwiseClone, Column)


            newColumn.Values = New Dictionary(Of String, PropertyValue)

            For Each kvp As KeyValuePair(Of String, PropertyValue) In Me.Values
                newColumn.Values.Add(kvp.Key, kvp.Value)
            Next

            Return newColumn

        End Function


        Public Sub New()
            Values.Add("NETDataType", New PropertyValue("", True))
            Values.Add("FixedName", New PropertyValue("", True))
            Values.Add("FKTFieldName", New PropertyValue("", True))
            Values.Add("FKTNetDataType", New PropertyValue("", True))
            Values.Add("FTKEY", New PropertyValue("", True))
            Values.Add("KeyType", New PropertyValue("", True))
            Values.Add("FKTABLE_NAME", New PropertyValue("", True))

            Values.Add("AssociationType", New PropertyValue("", True))
            Values.Add("IsAssociation", New PropertyValue("False", True))
            Values.Add("FKTABLE_ALIAS", New PropertyValue("", True))
            Values.Add("FKPopulateLocal", New PropertyValue("False", True))
            Values.Add("FKPopulateLocalField", New PropertyValue("", True))
            Values.Add("FKTFieldNameAlias", New PropertyValue("", True))
            Values.Add("FKDirectStatement", New PropertyValue("False", True))

            Values.Add("FKSelectMethod", New PropertyValue("", True))
            Values.Add("Width", New PropertyValue("", True))
            Values.Add("AltFieldName", New PropertyValue("", True))
            Values.Add("Description", New PropertyValue("", True))

            Values.Add("Format", New PropertyValue("", True))
            Values.Add("Format2", New PropertyValue("", True))
            Values.Add("Format3", New PropertyValue("", True))

            Values.Add("Index", New PropertyValue("False", True))
            Values.Add("Validation1", New PropertyValue("", True))
            Values.Add("Validation2", New PropertyValue("", True))
            Values.Add("ValidationMessage", New PropertyValue("", True))
            Values.Add("Message", New PropertyValue("", True))
            Values.Add("FKDataSource", New PropertyValue("Foreign table object source", True))

            Values.Add("MultiLine", New PropertyValue("False", True))
            Values.Add("CharWidth", New PropertyValue("0", True))
            Values.Add("CharRows", New PropertyValue("0", True))
            Values.Add("PixelWidth", New PropertyValue("0", True))
            Values.Add("PixelHeight", New PropertyValue("0", True))

            Values.Add("MultiLine2", New PropertyValue("False", True))
            Values.Add("CharWidth2", New PropertyValue("0", True))
            Values.Add("CharRows2", New PropertyValue("0", True))
            Values.Add("PixelWidth2", New PropertyValue("0", True))
            Values.Add("PixelHeight2", New PropertyValue("0", True))

            Values.Add("MultiLine3", New PropertyValue("False", True))
            Values.Add("CharWidth3", New PropertyValue("0", True))
            Values.Add("CharRows3", New PropertyValue("0", True))
            Values.Add("PixelWidth3", New PropertyValue("0", True))
            Values.Add("PixelHeight3", New PropertyValue("0", True))

            Values.Add("Custom1", New PropertyValue("", True))
            Values.Add("Custom2", New PropertyValue("", True))
            Values.Add("Custom3", New PropertyValue("", True))
            Values.Add("Custom4", New PropertyValue("", True))

            Values.Add("Snippet1", New PropertyValue("", True))
            Values.Add("Snippet2", New PropertyValue("", True))

            Values.Add("DefaultValue", New PropertyValue("", True))
            Values.Add("DefaultEmptyString", New PropertyValue("False", True))

            Values.Add("XCoord", New PropertyValue("0", True))
            Values.Add("YCoord", New PropertyValue("0", True))
            Values.Add("XCoord2", New PropertyValue("0", True))
            Values.Add("YCoord2", New PropertyValue("0", True))
            Values.Add("XCoord3", New PropertyValue("0", True))
            Values.Add("YCoord3", New PropertyValue("0", True))

            Values.Add("Flag1", New PropertyValue("False", True))
            Values.Add("Flag2", New PropertyValue("False", True))
            Values.Add("Flag3", New PropertyValue("False", True))
            Values.Add("Flag4", New PropertyValue("False", True))
            Values.Add("Flag5", New PropertyValue("False", True))
            Values.Add("Flag6", New PropertyValue("False", True))
            Values.Add("Flag7", New PropertyValue("False", True))
            Values.Add("Flag8", New PropertyValue("False", True))
            Values.Add("Flag9", New PropertyValue("False", True))
            Values.Add("Flag10", New PropertyValue("False", True))
            Values.Add("Flag11", New PropertyValue("False", True))
            Values.Add("Flag12", New PropertyValue("False", True))
            Values.Add("Flag13", New PropertyValue("False", True))
            Values.Add("Flag14", New PropertyValue("False", True))
            Values.Add("Flag15", New PropertyValue("False", True))
            Values.Add("Flag16", New PropertyValue("False", True))
            Values.Add("Flag17", New PropertyValue("False", True))
            Values.Add("Flag18", New PropertyValue("False", True))
            Values.Add("Flag19", New PropertyValue("False", True))
            Values.Add("Flag20", New PropertyValue("False", True))

            Values.Add("IS_NULLABLE", New PropertyValue("False", True))
            Values.Add("ISIDENTITY", New PropertyValue("False", True))

            Values.Add("FKDropDownEmptyMessage", New PropertyValue("", True))
            Values.Add("TabStop", New PropertyValue("", True))
            Values.Add("ControlType", New PropertyValue("", True))

            Values.Add("FKConstraintName", New PropertyValue("", True))
            Values.Add("FKTableSchema", New PropertyValue("", True))
            Values.Add("DATA_TYPE", New PropertyValue("", True))
            Values.Add("COLUMN_DEFAULT", New PropertyValue("", True))
            Values.Add("COLLATION_NAME", New PropertyValue("", True))
            Values.Add("NUMERIC_SCALE", New PropertyValue("", True))
            Values.Add("NUMERIC_PRECISION", New PropertyValue("", True))
            Values.Add("ORDINAL_POSITION", New PropertyValue("", True))
            Values.Add("SPParamType", New PropertyValue("", True))
            Values.Add("IsDirty", New PropertyValue("False", True))

        End Sub
    End Class
#End Region

#Region "Public Class Condition"
    <Serializable()> Public Class Condition
        'default flag
        'datatype
        'template file
        Dim _Property As String, _Operator As String, _Value As String

        Public Property CompValue() As String
            Get
                Return _Value
            End Get

            Set(ByVal Value As String)
                _Value = Value
            End Set
        End Property


        Public Property CompOperator() As String
            Get
                Return _Operator
            End Get

            Set(ByVal Value As String)
                _Operator = Value
            End Set
        End Property

        Public Property CompProperty() As String
            Get
                Return _Property
            End Get

            Set(ByVal Value As String)
                _Property = Value
            End Set
        End Property

    End Class
#End Region

#Region "Public Class PairValue"
    <Serializable()> Public Class PairValue
        'default flag
        'datatype
        'template file
        Dim _Property As String, _Value As String, _Type As String, _Custom As String

        Public Property Value() As String
            Get
                Return _Value
            End Get

            Set(ByVal Value As String)
                _Value = Value
            End Set


        End Property


        Public Property PropertyID() As String
            Get
                Return _Property
            End Get

            Set(ByVal Value As String)
                _Property = Value
            End Set
        End Property

        Public Property Type() As String
            Get
                Return _Type
            End Get

            Set(ByVal Value As String)
                _Type = Value
            End Set
        End Property

        Public Property Custom() As String
            Get
                Return _Custom
            End Get

            Set(ByVal Value As String)
                _Custom = Value
            End Set
        End Property


    End Class
#End Region

#Region "Public Class GenerateParameters"
    <Serializable()> Public Class GenerateParameters
        'default flag
        'datatype
        'template file
        Public Values As New Dictionary(Of String, String)

        Public FileName As String, _IsDirty As Boolean

        Public Property IsDirty() As Boolean
            Get
                Return _IsDirty
            End Get

            Set(ByVal Value As Boolean)
                _IsDirty = Value
            End Set
        End Property

        Public Sub New()
            'initialize default properties 
            Values.Add("TemplateFile", "")
            Values.Add("OutputDirectory", "")
            Values.Add("OutputLanguage", "VB")
            Values.Add("DatabaseFile", "")
            Values.Add("DatabaseName", "")
            Values.Add("TableName", "")
            Values.Add("Namespace", "")
            Values.Add("OutputFile", "")
            Values.Add("AuthorName", "")
            Values.Add("Modified", "")
            Values.Add("NameSpace", "")
            Values.Add("Assembly", "")
            Values.Add("ShortTime", "")
            Values.Add("LongTime", "")
            Values.Add("ClassName", "")
            Values.Add("FieldWidth", "")
            Values.Add("Version", "")
            Values.Add("FileVersion", "")
            Values.Add("Company", "")
            Values.Add("Trademark", "")
            Values.Add("ConnectionString", "")
            Values.Add("Process", "All Tables")
            Values.Add("BuildSingleFile", "False")

            _IsDirty = False
        End Sub
    End Class
#End Region

    Public Class EventErrorArgs
        Inherits EventArgs

        Public ex As Exception
        Public procedure As String, errormessage As String

        Public Sub New(ByVal exp As Exception, ByVal eprocedure As String, ByVal errormsg As String)
            Me.ex = exp
            Me.procedure = eprocedure
            Me.errormessage = errormsg

        End Sub


    End Class

    Public Class CodeGenException
        Inherits System.Exception
        ' Creates a constructor for the exception that allows you to set the 
        ' message property when thrown.
        Public Sub New(ByVal Message As String, ByVal exp As System.Exception)
            MyBase.New(Message, exp)

        End Sub
    End Class


#Region "Public Class GenerateCode"
    ''' <summary>
    ''' Generates output by processing templates
    ''' </summary>
    ''' <remarks></remarks>
    Public Class GenerateCode
        Public Values As New Dictionary(Of String, String)
        Private currentSection As String

        Private layout As LayoutTemplate, tableLayout As TableLayout, outputText As String
        Private dbServer As DatabaseServer, generateProperties As New GenerateParameters
        Private outputLanguage As String, lastField As Boolean, lastKey As String, lastNonKey As String
        Private ordinalAll As Integer = 0, ordinalMatched As Integer = 0, templateLevel As String, levelCondition As String
        Private engine As PythonEngine, buildSingleFile As Boolean
        Public LeftSearch As String = "<#", RightSearch As String = "#>", funcSeperator As String = "\|\"
        Private _scriptLogic As String, outputDirectory As String

        Public Property ScriptLogic() As String
            Get
                Return _scriptLogic
            End Get

            Set(ByVal Value As String)
                _scriptLogic = Value
            End Set
        End Property

        ''' <summary>
        ''' Load serialized template structure file
        ''' </summary>
        ''' <param name="fileName">Path of file to load</param>
        ''' <remarks></remarks>
        Public Sub LoadTemplate(Optional ByVal fileName As String = "")
            If fileName = "" Then fileName = generateProperties.Values("TemplateFile")

            Try
                'open file
                layout = CType(Utils.LoadSerializedFile(fileName), LayoutTemplate)

            Catch ex As Exception
                Throw New CodeGenException("Error loading template " & fileName, ex)
            End Try

        End Sub
        ''' <summary>
        ''' Load serialized database structure file
        ''' </summary>
        ''' <param name="fileName">Path of file to load</param>
        ''' <remarks></remarks>
        Public Sub LoadDatabaseStructure(Optional ByVal fileName As String = "")

            If fileName = "" Then fileName = generateProperties.Values("DatabaseFile")

            Try
                dbServer = CType(Utils.LoadSerializedFile(fileName), DatabaseServer)
            Catch ex As Exception
                ex.Source = "LoadDatabaseStructure"
                Throw New CodeGenException("Error loading database structure " & fileName, ex)

            End Try

        End Sub

        Public Delegate Sub ErrorOccurredEventHandler(ByVal sender As Object, ByVal e As EventErrorArgs)
        Public Event ErrorOccurredEvent As ErrorOccurredEventHandler

        ''' <summary>
        ''' Load serialized template generation property file
        ''' </summary>
        ''' <param name="fileName">Path of file to load</param>
        ''' <remarks></remarks>
        Public Sub LoadGenerateProperties(ByVal fileName As String)
            Try
                generateProperties = CType(Utils.LoadSerializedFile(fileName), GenerateParameters)
            Catch ex As Exception
                Dim CGenException As New CodeGenException("Error loading properties " & fileName, ex)
                CGenException.Source = "LoadGenerateProperties"
                Throw CGenException
            End Try

        End Sub
        ''' <summary>
        ''' Update template generation properties
        ''' </summary>
        ''' <param name="valKey">Key value to update</param>
        ''' <param name="newVal">New value to update</param>
        ''' <remarks></remarks>
        Public Sub ModifyProperty(ByVal valKey As String, ByVal newVal As Object)
            '!AddUpdateDictionary(generateProperties.Values, valKey, newVal)

            If generateProperties.Values.ContainsKey(valKey) Then
                generateProperties.Values(valKey) = newVal
            Else
                generateProperties.Values.Add(valKey, newVal)
            End If

        End Sub

        ''' <summary>
        ''' Updates dictionary value. If key doesn't exist adds to dictionary
        ''' </summary>
        ''' <param name="dict">Dictionary to update</param>
        ''' <param name="key">Dictionary key</param>
        ''' <param name="value">value to update or add</param>
        ''' <remarks></remarks>
        Sub AddUpdateDictionary(ByVal dict As Generic.Dictionary(Of String, PropertyValue), ByVal key As String, ByVal value As Object)

            If value Is Nothing Then
                value = ""
            End If

            If dict.ContainsKey(key) Then
                If value.GetType.Name = "String" Then
                    dict(key).Value = value
                Else
                    dict(key) = value
                End If

            Else

                If value.GetType.Name = "String" Then
                    dict.Add(key, New PropertyValue(value, False))
                Else
                    dict.Add(key, value)
                End If
            End If
        End Sub
        ''' <summary>
        ''' Builds variable declaration depending on language used
        ''' </summary>
        ''' <param name="buildLanguage">C# or VB</param>
        ''' <param name="variableName"></param>
        ''' <param name="dataType">.NET data type</param>
        ''' <returns>Built variable declaration</returns>
        ''' <remarks></remarks>
        Function BuildVariable(ByVal buildLanguage As String, ByVal variableName As String, ByVal dataType As String) As String

            If buildLanguage = "VB" Then
                BuildVariable = variableName & " AS " & dataType & ","
            Else 'assume C#

                dataType = GetNETType(buildLanguage, dataType)
                BuildVariable = dataType & " " & variableName & ","
            End If

        End Function
        ''' <summary>
        ''' Returns .NET code data type depending on language being used
        ''' </summary>
        ''' <param name="buildLanguage">VB or C#</param>
        ''' <param name="dataType">VB data type</param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Function GetNETType(ByVal buildLanguage As String, ByVal dataType As String) As String

            If buildLanguage = "VB" Then
                GetNETType = dataType
            Else 'assume C#

                'convert a few VB data types to C#
                Select Case dataType
                    Case "Boolean"
                        dataType = "bool"
                    Case "Integer"
                        dataType = "int"
                    Case "Single"
                        dataType = "float"
                    Case "Single"
                        dataType = "float"
                    Case "String"
                        dataType = "string"
                    Case "Date"
                        dataType = "DateTime"
                End Select

                GetNETType = dataType
            End If

        End Function
        ''' <summary>
        ''' Generates output based on template parameters
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub GenerateOutput()
            'table information to generation properties
            engine = New PythonEngine
            engine.Import("Site")
            engine.Execute(_scriptLogic)

            'table information to generation properties
            engine.Globals("DataSourceName") = dbServer.Databases(generateProperties.Values("DatabaseName")).DataSourceName & ""
            engine.Globals("DBConnectionString") = dbServer.Databases(generateProperties.Values("DatabaseName")).ConnectionString & ""
            engine.Globals("ServerName") = dbServer.Name
            engine.Globals("ServerUserName") = dbServer.UserName
            engine.Globals("ServerPassword") = dbServer.Password



            'determine language (C# or VB)
            outputLanguage = generateProperties.Values("OutputLanguage")

            buildSingleFile = CBool(generateProperties.Values("BuildSingleFile"))
            outputText = ""

            If generateProperties.Values("Process") = "All Tables" Then

                For Each kvp As KeyValuePair(Of String, TableLayout) In dbServer.Databases(generateProperties.Values("DatabaseName")).TableLayouts

                    If kvp.Value.Properties("IgnoreTable").Value = "False" And kvp.Value.Properties("IsView").Value = "False" Then
                        BuildTableOutput(kvp.Key)
                    End If
                Next

            Else
                BuildTableOutput(generateProperties.Values("TableName"))
            End If

            If buildSingleFile Then
                'attempt to write the results to text file
                My.Computer.FileSystem.WriteAllText(outputDirectory & engine.Globals("OutputFile").ToString, outputText, False)
            End If


        End Sub

        ''' <summary>
        ''' Generates output based on template parameters
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub BuildTableOutput(ByVal tableName As String)
            Dim templateSection As SectionTemplate, col As Column
            Dim allFields As String = String.Empty, allFieldsNoKeys As String = String.Empty
            Dim allKeys As String = String.Empty, dataType As String, columnCount As Integer, firstRun As Boolean = True, processColumn As Boolean
            Dim resultText As String

            'get the table layout 
            tableLayout = dbServer.Databases(generateProperties.Values("DatabaseName")).TableLayouts(tableName)

            'loop through each generation property and update any values that are 
            Dim duplicateDictionary As New Dictionary(Of String, String)

            'need to duplicate dictionary in order to update..
            For Each kvp As KeyValuePair(Of String, String) In generateProperties.Values
                engine.Globals(kvp.Key) = kvp.Value
            Next

            engine.Globals("TableName") = tableLayout.Name
            engine.Globals("TableDescription") = tableLayout.Description
            engine.Globals("TableAltName") = tableLayout.Properties("TableAltName").Value
            engine.Globals("TableAltNameB") = tableLayout.Properties("TableAltNameB").Value
            engine.Globals("SelectSQL") = tableLayout.Properties("SelectSQL").Value
            engine.Globals("TableFlag1") = tableLayout.Properties("TableFlag1").Value
            engine.Globals("TableFlag2") = tableLayout.Properties("TableFlag2").Value
            engine.Globals("TableFlag3") = tableLayout.Properties("TableFlag3").Value
            engine.Globals("TableFlag4") = tableLayout.Properties("TableFlag4").Value
            engine.Globals("TableFlag5") = tableLayout.Properties("TableFlag5").Value
            engine.Globals("TableFlag6") = tableLayout.Properties("TableFlag6").Value
            engine.Globals("TableFlag7") = tableLayout.Properties("TableFlag7").Value
            engine.Globals("TableFlag8") = tableLayout.Properties("TableFlag8").Value
            engine.Globals("TableFlag9") = tableLayout.Properties("TableFlag9").Value
            engine.Globals("TableFlag10") = tableLayout.Properties("TableFlag10").Value

            engine.Globals("TableMessage1") = tableLayout.Properties("TableMessage1").Value
            engine.Globals("TableMessage2") = tableLayout.Properties("TableMessage2").Value
            engine.Globals("TableMessage3") = tableLayout.Properties("TableMessage3").Value
            engine.Globals("TableMessage4") = tableLayout.Properties("TableMessage4").Value

            engine.Globals("TableComment1") = tableLayout.Properties("TableComment1").Value
            engine.Globals("SQLSelectFields") = tableLayout.Properties("SQLSelectFields").Value
            engine.Globals("SQLFromStatement") = tableLayout.Properties("SQLFromStatement").Value

            'build field global properties
            For Each col In tableLayout.Columns

                If Not col.Ignore Then

                    AddUpdateDictionary(col.Values, "ProperNETDataType", col.Values("NETDataType").Value)
                    AddUpdateDictionary(col.Values, "BaseDataType", col.BaseDataType)
                    AddUpdateDictionary(col.Values, "GenericType", col.GenericType)

                    dataType = GetNETType(outputLanguage, col.Values("NETDataType").Value)

                    col.Values("NETDataType").Value = dataType

                    allFields = allFields & BuildVariable(outputLanguage, col.Name, dataType)

                    If Not col.IsKey Then
                        allFieldsNoKeys = allFieldsNoKeys & BuildVariable(outputLanguage, col.Name, dataType)
                    End If

                    If col.IsKey Then
                        allKeys = allKeys & BuildVariable(outputLanguage, col.Name, dataType)
                    End If

                End If
            Next
            'strip commas from field variables
            allFields = allFields.TrimEnd(CChar(","))
            allKeys = allKeys.TrimEnd(CChar(","))
            allFieldsNoKeys = allFieldsNoKeys.TrimEnd(CChar(","))

            'add field values to property collection
            engine.Globals("AllFieldsNoKeys") = allFieldsNoKeys
            engine.Globals("AllFields") = allFields
            engine.Globals("AllKeys") = allKeys

            'update date time properties
            engine.Globals("LongDate") = Now.ToLongDateString
            engine.Globals("ShortDate") = Now.ToShortDateString
            engine.Globals("LongTime") = Now.ToLongTimeString
            engine.Globals("ShorTime") = Now.ToShortTimeString
            engine.Globals("FieldCount") = tableLayout.Columns.Count.ToString

            Dim expression As String

            'if single file flag not set then build new output
            If Not buildSingleFile Then
                outputText = ""
            End If


            'search each variable and translate any expressions..
            For Each kvp As KeyValuePair(Of String, String) In generateProperties.Values
                If Not kvp.Value Is Nothing Then
                    For Each matchedText As Match In Regex.Matches(kvp.Value.ToString, LeftSearch & "\w*" & RightSearch)
                        expression = matchedText.ToString
                        expression = expression.Substring(2, expression.Length - 4)
                        expression = engine.Evaluate(expression).ToString()
                        engine.Globals(kvp.Key) = kvp.Value.Replace(matchedText.ToString, expression)
                    Next
                End If
            Next

            outputDirectory = engine.Globals("OutputDirectory").ToString
            If Not outputDirectory.TrimEnd.EndsWith("\") Then
                outputDirectory = outputDirectory & "\"
            End If

            outputText = outputText & ProcessTemplate(layout.PrefixTemplate)

            'determine what the last key and non-key field in the table
            For Each col In tableLayout.Columns
                If col.IsKey Then lastKey = col.Name

                lastNonKey = col.Name
            Next

            'process each section
            For Each templateSection In layout.Sections

                'check section conditions
                If EvalSectionCondition(templateSection, tableLayout) Then

                    'loop through properties and replace any values
                    outputText = outputText & ProcessTemplate(templateSection.PrefixTemplate)

                    lastField = False
                    columnCount = 1
                    ordinalAll = 0
                    ordinalMatched = -1

                    firstRun = True
                    processColumn = True
                    currentSection = ""
                    Do While processColumn
                        For Each col In tableLayout.Columns
                            'col = kvptl.Value


                            If Not col.Ignore Then

                                For Each kvp As KeyValuePair(Of String, PropertyValue) In col.Values
                                    engine.Globals(kvp.Key) = kvp.Value.Value
                                Next

                                'update ordinal position
                                AddUpdateDictionary(col.Values, "Ordinal", ordinalAll.ToString)
                                AddUpdateDictionary(col.Values, "IsPrimaryKey", ConvBool(col.IsKey.ToString, outputLanguage))
                                AddUpdateDictionary(col.Values, "IsForeignKey", ConvBool(col.IsForeignKey.ToString, outputLanguage))
                                AddUpdateDictionary(col.Values, "IsVirtualField", ConvBool(col.IsVirtualField.ToString, outputLanguage))
                                AddUpdateDictionary(col.Values, "IS_NULLABLE", ConvBool(col.Values("IS_NULLABLE").Value, outputLanguage))
                                AddUpdateDictionary(col.Values, "ISIDENTITY", ConvBool(col.Values("ISIDENTITY").Value, outputLanguage))

                                engine.Globals("Ordinal") = ordinalAll
                                engine.Globals("lastField") = lastField
                                engine.Globals("IsPrimaryKey") = col.IsKey
                                engine.Globals("IsForeignKey") = col.IsForeignKey
                                engine.Globals("IsVirtualField") = col.IsVirtualField
                                engine.Globals("IS_NULLABLE") = CBool(ConvBool(col.Values("IS_NULLABLE").Value, outputLanguage))
                                engine.Globals("ISIDENTITY") = CBool(ConvBool(col.Values("ISIDENTITY").Value, outputLanguage))
                                engine.Globals("IsAssociation") = CBool(ConvBool(col.Values("IsAssociation").Value, outputLanguage))

                                'ISIDENTITY 
                                engine.Globals("Ignore") = col.Ignore
                                engine.Globals("NativeFieldType") = col.NativeFieldType

                                resultText = ProcessColTemplate(templateSection, col, firstRun)
                                If Not firstRun Then
                                    outputText = outputText & resultText
                                    ordinalAll = ordinalAll + 1
                                End If

                            End If

                            columnCount = columnCount + 1
                        Next

                        processColumn = firstRun
                        firstRun = False
                        columnCount = 1
                        ordinalAll = 0
                        ordinalMatched = -1

                    Loop

                    outputText = outputText & ProcessTemplate(templateSection.PostFixTemplate)

                End If

            Next
            outputText = outputText & ProcessTemplate(layout.PostFixTemplate)

            Try
                If Not buildSingleFile Then
                    'attempt to write the results to text file
                    My.Computer.FileSystem.WriteAllText(outputDirectory & engine.Globals("OutputFile").ToString, outputText, False)
                End If

            Catch ex As Exception

                Dim CGenException As New CodeGenException("Error writing file " & outputDirectory & generateProperties.Values("OutputFile"), ex)
                CGenException.Source = "GenerateOutput"
                Throw CGenException

            End Try


        End Sub




        Function EvalSectionCondition(ByVal sectionTemplate As SectionTemplate, ByVal tableLayout As TableLayout) As Boolean
            Dim fieldValue As String, bMatch As Boolean = True
            If Not sectionTemplate.Conditions Is Nothing AndAlso sectionTemplate.Conditions.Count > 0 Then

                For Each condition As Condition In sectionTemplate.Conditions

                    fieldValue = tableLayout.Properties(condition.CompProperty).Value

                    Select Case condition.CompOperator
                        Case "Equals (=)"
                            If Not String.IsNullOrEmpty(condition.CompValue) AndAlso fieldValue = condition.CompValue Then
                                bMatch = True
                            Else
                                bMatch = False
                                Exit For
                            End If

                        Case "Property Exists"
                            '"Equals (=)", "Property Exists", "Is Not Empty/blank", "Greater Than (>)", "Less Than (<)"})

                    End Select
                Next
            End If

            EvalSectionCondition = bMatch
        End Function

        Function ConvBool(ByVal value As String, ByVal language As String) As String
            Dim booleanVal As Boolean

            value = Trim(value.ToUpper)
            If value = "YES" Or value = "TRUE" Or value = "1" Then
                value = "True"
            ElseIf value = "NO" Or value = "FALSE" Or value = "0" Then
                value = "False"
            Else
                value = "False"
            End If
            booleanVal = CBool(value)

            ConvBool = booleanVal.ToString
            If language = "C#" Then
                ConvBool = ConvBool.ToLower
            End If

        End Function



        ''' <summary>
        ''' Update template with property values
        ''' </summary>
        ''' <param name="templateText"></param>
        ''' <returns>Update template</returns>
        ''' <remarks></remarks>
        Function ProcessTemplate(ByVal templateText As String) As String

            Dim expression As String

            For Each matchedText As Match In Regex.Matches(templateText, LeftSearch & "\w*" & RightSearch)
                expression = matchedText.ToString
                expression = expression.Substring(2, expression.Length - 4)

                Try
                    expression = engine.Evaluate(expression).ToString()

                Catch ex As Exception

                    MsgBox(ex.Message)
                    Throw New CodeGenException("Error executing expression " & expression & " in process template", ex)
                End Try


                templateText = templateText.Replace(matchedText.ToString, expression)
            Next

            ProcessTemplate = templateText

            Exit Function
            For Each kvp As KeyValuePair(Of String, String) In generateProperties.Values
                templateText = templateText.Replace(LeftSearch & kvp.Key & RightSearch, kvp.Value)
            Next

            ProcessTemplate = templateText
        End Function

        Function ProcessColTemplate(ByVal templateSection As SectionTemplate, ByVal col As Column, ByVal firstRun As Boolean) As String
            Dim replaceTemplate As String, dataType As String, currentLevel As Integer, process As Boolean
            Dim dt As DataType, bMatch As Boolean
            Dim processedTemplate As String = ""


            dataType = col.GenericType ' col.Values("DATA_TYPE").Value
            currentSection = templateSection.Description


            If lastNonKey = col.Name Then
                lastField = True
                engine.Globals("lastField") = lastField
            End If

            currentLevel = 1
            process = True
            Do While process
                replaceTemplate = ""
                process = False

                'if specific data type criteria then check each field
                If templateSection.DataTypes.Count > 0 Then
                    'check each data type
                    For Each dt In templateSection.DataTypes
                        templateLevel = dt.Description

                        If dt.Level = currentLevel Then

                            'CompareValidator string date double integer currency
                            replaceTemplate = String.Empty
                            bMatch = False
                            If dt.Type = dataType Then
                                bMatch = True
                            ElseIf dt.Type = "String Types" And col.BaseDataType = "String" Then
                                bMatch = True
                            ElseIf dt.Type = "Integer Types" And col.BaseDataType = "Integer" Then
                                bMatch = True
                            ElseIf dt.Type = "Decimal Type" And col.BaseDataType = "Double" Then
                                bMatch = True
                            ElseIf dt.Type = "Date Types" And col.BaseDataType = "Date" Then
                                bMatch = True
                            ElseIf dt.Type = "Currency Types" And col.BaseDataType = "Currency" Then
                                bMatch = True
                            ElseIf dt.Type = "Any Data Type" Then
                                bMatch = True
                            End If

                            'determine if correct type of field
                            If bMatch Then

                                lastField = False
                                If lastNonKey = col.Name Then
                                    lastField = True
                                End If

                                If dt.FieldType = "All fields" Then
                                    bMatch = True
                                ElseIf dt.FieldType = "All fields except primary keys" Then
                                    If col.IsKey Then
                                        bMatch = False
                                    End If

                                ElseIf dt.FieldType = "Primary keys only" Then
                                    If Not col.IsKey Then
                                        bMatch = False
                                    End If

                                    'determine if last column in collection
                                    If col.Name = lastKey Then
                                        lastField = True
                                    Else
                                        lastField = False
                                    End If

                                ElseIf dt.FieldType = "Foreign keys only" Then
                                    If Not col.IsForeignKey Then
                                        bMatch = False
                                    End If

                                ElseIf dt.FieldType = "Associations" Then
                                    If Not col.Values("IsAssociation").Value = "True" Then
                                        bMatch = False
                                    End If

                                ElseIf dt.FieldType = "Virtual Fields" Then
                                    If Not col.IsVirtualField Then
                                        bMatch = False
                                    End If

                                End If

                            End If

                            engine.Globals("lastField") = lastField

                            Dim fieldValue As String
                            If bMatch And Not dt.Conditions Is Nothing AndAlso dt.Conditions.Count > 0 Then

                                For Each condition As Condition In dt.Conditions

                                    Try
                                        fieldValue = engine.Globals(condition.CompProperty).ToString
                                    Catch ex As Exception
                                        Throw New CodeGenException("Error looking up field value  " & condition.CompProperty & " in condition " & col.Name & " in template " & templateSection.Description, ex)
                                    End Try


                                    Select Case condition.CompOperator
                                        Case "Equals (=)"
                                            If Not String.IsNullOrEmpty(condition.CompValue) AndAlso StrComp(fieldValue, condition.CompValue, CompareMethod.Text) = 0 Then 'fieldValue. = condition.CompValue Then
                                                bMatch = True
                                                'if OR operator then exit
                                                If dt.OrField Then Exit For

                                            Else
                                                bMatch = False
                                                'if AND operator then exit
                                                If Not dt.OrField Then Exit For
                                            End If

                                        Case "Property Exists"

                                    End Select
                                Next

                                If bMatch And dt.PropertyOperator <> "" Then

                                    If dt.PropertyOperator = "Increase Column Ordinal/Index" Then
                                        ordinalAll = ordinalAll + 1
                                    End If

                                End If

                            End If

                            If bMatch Then
                                replaceTemplate = dt.Template
                                currentLevel = currentLevel + 1
                                ordinalMatched = ordinalMatched + 1
                                AddUpdateDictionary(col.Values, "OrdinalMatched", ordinalMatched.ToString)
                                engine.Globals("OrdinalMatched") = ordinalMatched


                                If firstRun And Not dt.EmptyField Then
                                    lastNonKey = col.Name
                                End If

                                If firstRun And Not dt.EmptyField And col.IsKey Then
                                    lastKey = col.Name
                                End If

                                process = True
                                Exit For
                            End If

                        End If

                    Next 'data types loop
                Else
                    'get the default section template
                    replaceTemplate = templateSection.Detail
                End If

                If firstRun Then
                    ProcessColTemplate = ""
                    Exit Function
                End If


                'if template is empty  then don't bother processing
                If Not String.IsNullOrEmpty(replaceTemplate) Then

                    replaceTemplate = replaceTemplate.Replace(LeftSearch & "FieldName" & RightSearch, col.Name)
                    processedTemplate = processedTemplate & ReplaceFunctions(replaceTemplate, col)
                End If

            Loop


            ProcessColTemplate = processedTemplate
        End Function

        Function ProcessExpressions(ByVal procString As String, ByRef lastLength As String, ByRef currentCol As Column) As String
            Dim processedValue As String = "", stringLocation As Integer, returnString As String, returnLength As Integer

            Do While stringLocation + LeftSearch.Length <= procString.Length
                Try

                    If procString.Substring(stringLocation, LeftSearch.Length) = LeftSearch Then
                        returnString = ProcessExpressions(procString.Substring(stringLocation + LeftSearch.Length), returnLength, currentCol)
                        processedValue = processedValue & EvalFunctions(returnString, currentCol)
                        stringLocation = stringLocation + returnLength
                        If (stringLocation + LeftSearch.Length > procString.Length) Then
                            Return processedValue & procString.Substring(stringLocation)
                        End If


                    ElseIf procString.Substring(stringLocation, LeftSearch.Length) = RightSearch Then
                        lastLength = stringLocation + LeftSearch.Length * 2
                        Return processedValue

                    Else
                        processedValue = processedValue & procString.Substring(stringLocation, 1)
                        stringLocation = stringLocation + 1
                    End If
                Catch ex As Exception
                    If Not ex.InnerException Is Nothing Then
                        Throw ex
                    Else
                        Throw New Exception("Error in ProcessExpressions in section" & currentSection & ", detail level " & templateLevel, ex)
                    End If
                End Try
            Loop

            processedValue = processedValue & procString.Substring(stringLocation)
            Return processedValue
        End Function


        Public Function EvalFunctions(ByVal functionName As String, ByVal currentCol As Column) As String
            Dim functionEnd As Integer ', fullFunction As String

            Try
                If Left(functionName, 3).ToUpper = "REP" Then
                    If Not lastField Then
                        EvalFunctions = functionName.Substring(3)
                    Else
                        EvalFunctions = ""
                    End If

                ElseIf Left(functionName, 10).ToUpper = "ISNULLABLE" Then
                    Dim charPos As Integer
                    charPos = InStr(10 + funcSeperator.Length, functionName, funcSeperator) - 1 '+ funcSeperator.Length - 1

                    '
                    If currentCol.Values("IS_NULLABLE").Value.ToUpper = "YES" Or currentCol.Values("IS_NULLABLE").Value.ToUpper = "TRUE" Then
                        EvalFunctions = functionName.Substring(10 + funcSeperator.Length, charPos - (10 + funcSeperator.Length))

                    Else
                        EvalFunctions = functionName.Substring(charPos + funcSeperator.Length)
                    End If
                    functionEnd = 1

                Else

                    Dim charPos As Integer, returnValue As Object, charPosEnd As Integer

                    charPos = InStr(functionName, funcSeperator)

                    'if function seperator is found then evaluate the string before the seperator
                    If charPos > 0 Then
                        returnValue = engine.Evaluate(functionName.Substring(0, charPos - 1))

                        If CBool(returnValue) Then
                            charPosEnd = InStr(charPos + funcSeperator.Length, functionName, funcSeperator)

                            If charPosEnd > 0 Then
                                returnValue = functionName.Substring(charPos + funcSeperator.Length - 1, charPosEnd - (charPos + funcSeperator.Length))
                            Else
                                returnValue = functionName.Substring(charPos + funcSeperator.Length - 1)
                            End If

                        Else
                            charPosEnd = InStr(charPos + funcSeperator.Length, functionName, funcSeperator)

                            'check if there's a False value..
                            If charPosEnd > 0 Then
                                returnValue = functionName.Substring(charPosEnd + funcSeperator.Length - 1)
                            Else
                                returnValue = ""
                            End If

                        End If
                    Else
                        returnValue = engine.Evaluate(functionName)
                    End If

                    EvalFunctions = returnValue
                End If

            Catch ex As Exception
                Throw New Exception("Error in EvalFunctions in section " & currentSection & ", detail level " & templateLevel & vbCrLf & ex.Message, ex)
            End Try

        End Function

        Public Function ReplaceFunctions(ByVal replaceTemplate As String, ByVal currentCol As Column) As String
            ReplaceFunctions = ProcessExpressions(replaceTemplate, 0, currentCol)
        End Function


        Public Property GenerateParameters() As GenerateParameters
            Get
                Return generateProperties
            End Get
            Set(ByVal Value As GenerateParameters)
                generateProperties = Value
            End Set
        End Property


    End Class
#End Region
    ''' <summary>
    ''' General utility function
    ''' </summary>
    ''' <remarks></remarks>
    Public Class Utils

        Public Shared Function LoadSerializedFile(ByVal strFileName As String) As Object
            Dim f As New BinaryFormatter
            Dim s As FileStream

            Try
                'open file
                s = New FileStream(strFileName, FileMode.Open)

            Catch ex As Exception

                Throw ex

            End Try

            If s Is Nothing Then
                LoadSerializedFile = Nothing
            Else
                LoadSerializedFile = f.Deserialize(s)
                LoadSerializedFile.FileName = strFileName
            End If
            s.Close()
        End Function

        Public Shared Sub SaveFile(ByVal strPath As String, ByVal serializeobject As Object)
            Dim s As Stream
            Dim b As BinaryFormatter = New BinaryFormatter()

            s = New FileStream(strPath, FileMode.Create)

            b.Serialize(s, serializeobject)
            s.Close()

        End Sub

    End Class


    Public MustInherit Class DataSchema
        Protected nF As Integer
        Protected strSQLType As String = ""
        Protected strFieldName As String, newcol As Column, strSQL As String, colAlreadyExists As Boolean
        Protected columndictionary As Dictionary(Of String, String), columnList As New Generic.Dictionary(Of String, String)
        Protected reader As IDataReader
        Protected _columnTypes As New Dictionary(Of String, String), _columnDescriptions As New Dictionary(Of String, String)
        Protected _foreignKeys As New Dictionary(Of String, String()), _primaryKeys As New Dictionary(Of String, String)
        Protected _foreignTables As New List(Of String()), _storedProcedure As String, _dbPort As String

        Protected _columnsList As New Generic.List(Of Generic.Dictionary(Of String, String))
        Protected _spParameters As New Generic.List(Of Generic.Dictionary(Of String, String))

        Protected _indexes As New Dictionary(Of String, Index)

        Protected _BuildEntities As Boolean, _Server As String, _TableName As String, _UserID As String, _Password As String, _UseAuthentication As Boolean, _DatabaseName As String, _ConnectionString As String, _isView As Boolean, _TableSchema As String

        Protected _providerName As String

        Public ReadOnly Property GetReader() As IDataReader
            Get
                Return reader
            End Get

        End Property

        Public ReadOnly Property Indexes() As Dictionary(Of String, Index)
            Get
                Return _indexes
            End Get

        End Property

        Public ReadOnly Property ForeignKeys() As Dictionary(Of String, String())
            Get
                Return _foreignKeys
            End Get

        End Property

        Public ReadOnly Property PrimaryKeys() As Dictionary(Of String, String)
            Get
                Return _primaryKeys
            End Get

        End Property
        Public ReadOnly Property ForeignTables() As List(Of String())
            Get
                Return _foreignTables
            End Get

        End Property


        Public ReadOnly Property ColumnDescriptions() As Dictionary(Of String, String)
            Get
                Return _columnDescriptions
            End Get

        End Property

        Public ReadOnly Property ColumnTypes() As Dictionary(Of String, String)
            Get
                Return _columnTypes
            End Get

        End Property

        Public ReadOnly Property ColumnsList() As Generic.List(Of Generic.Dictionary(Of String, String))
            Get
                Return _columnsList
            End Get

        End Property

        Public Property Server() As String
            Get
                Return _Server
            End Get

            Set(ByVal Value As String)
                _Server = Value
            End Set
        End Property

        Public Property TableName() As String
            Get
                Return _TableName
            End Get

            Set(ByVal Value As String)
                _TableName = Value
            End Set
        End Property

        Public Property TableSchema() As String
            Get
                Return _TableSchema
            End Get

            Set(ByVal Value As String)
                _TableSchema = Value
            End Set
        End Property


        Public Property StoredProcedureName() As String
            Get
                Return _storedProcedure
            End Get

            Set(ByVal Value As String)
                _storedProcedure = Value
            End Set
        End Property


        Public Property IsView() As Boolean
            Get
                Return _isView
            End Get

            Set(ByVal Value As Boolean)
                _isView = Value
            End Set
        End Property

        Public Property UserID() As String
            Get
                Return _UserID
            End Get

            Set(ByVal Value As String)
                _UserID = Value
            End Set
        End Property

        Public Property Port() As String
            Get
                Return _dbPort
            End Get

            Set(ByVal Value As String)
                _dbPort = Value
            End Set
        End Property

        Public Property Password() As String
            Get
                Return _Password
            End Get

            Set(ByVal Value As String)
                _Password = Value
            End Set
        End Property

        Public Property UseAuthentication() As Boolean
            Get
                Return _UseAuthentication
            End Get

            Set(ByVal Value As Boolean)
                _UseAuthentication = Value
            End Set
        End Property


        Public ReadOnly Property ProviderName() As String
            Get
                Return _providerName
            End Get

        End Property


        Public Property DatabaseName() As String
            Get
                Return _DatabaseName
            End Get

            Set(ByVal Value As String)
                _DatabaseName = Value
            End Set
        End Property

        ''' <summary>
        ''' Build server connection string from object properties
        ''' </summary>
        ''' <remarks></remarks>
        MustOverride Sub BuildConnectionString()

        Public Property ConnectionString() As String
            Get
                BuildConnectionString()

                Return _ConnectionString
            End Get

            Set(ByVal Value As String)
                _ConnectionString = Value
            End Set
        End Property

        Public Property BuildEntities() As Boolean
            Get
                Return _BuildEntities
            End Get

            Set(ByVal Value As Boolean)
                _BuildEntities = Value
            End Set
        End Property




        ''' <summary>
        ''' Populate table information 
        ''' </summary>
        ''' <remarks></remarks>
        Public MustOverride Sub PopulateTableInfo()

        ''' <summary>
        ''' Get .NET datatypes from table
        ''' </summary>
        ''' <remarks></remarks>
        Public MustOverride Sub PopulateDataTypes()

        ''' <summary>
        ''' Get column descriptions (if supported)
        ''' </summary>
        ''' <remarks></remarks>
        Public MustOverride Sub PopulateDescriptions()

        Public MustOverride Sub PopulateIndexes()

        ''' <summary>
        ''' Get primary keys for specific table
        ''' </summary>
        ''' <remarks></remarks>
        Public MustOverride Sub PopulatePrimaryKeys()

        ''' <summary>
        ''' Determine if table entry is a view
        ''' </summary>
        ''' <remarks></remarks>
        Public MustOverride Sub CheckIfView()

        ''' <summary>
        ''' Get foreign keys for specific table
        ''' </summary>
        ''' <remarks></remarks>
        Public MustOverride Sub PopulateForeignKeys()

        Public MustOverride Sub PopulateFields()

        ''' <summary>
        ''' Get databases for specified server
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public MustOverride Function GetDatabases() As Generic.List(Of String)



        ''' <summary>
        ''' Get tables for specified database
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public MustOverride Function GetTables() As Generic.List(Of DBObject)

        ''' <summary>
        ''' Get store procedures for specified database
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public MustOverride Function GetStoredProcedures() As Generic.List(Of DBObject)
        Public MustOverride Sub PopulateParameters()


        ''' <summary>
        ''' Get generic data type based on native database type 
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public MustOverride Function GetDataType(ByVal strNativeType As String) As String

        ''' <summary>
        ''' Get generic column data type based on native database column type 
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public MustOverride Function GetGenericColumnType(ByVal strNativeType As String) As String


    End Class


#Region "Public Class ConfigSettings"
    <Serializable()> Public Class ConfigSettings
        'default flag
        'datatype
        'template file
        Dim _Type As String, _ColumnName As String
        Public Values As New Dictionary(Of String, String), PairValues As New Dictionary(Of String, String())

        Public FieldFunctions As New BindingList(Of PairValue)
        Public PropertyCustomizations As New BindingList(Of PairValue)
        Private _SettingsFileName As String = String.Empty


        Public Property FileName() As String
            Get
                Return _SettingsFileName
            End Get

            Set(ByVal Value As String)
                _SettingsFileName = Value
            End Set
        End Property

        Public Sub New()

            Values.Add("Custom1", "")
            Values.Add("Custom2", "")
            Values.Add("Custom3", "")
            Values.Add("Custom4", "")

            PairValues.Add("Flag1", New String() {"Flag 1", ""})
            PairValues.Add("Flag2", New String() {"Flag 2", ""})
            PairValues.Add("Flag3", New String() {"Flag 3", ""})
            PairValues.Add("Flag4", New String() {"Flag 4", ""})
            PairValues.Add("Flag5", New String() {"Flag 5", ""})
            PairValues.Add("Flag6", New String() {"Flag 6", ""})
            PairValues.Add("Flag7", New String() {"Flag 7", ""})
            PairValues.Add("Flag8", New String() {"Flag 8", ""})
            PairValues.Add("Flag9", New String() {"Flag 9", ""})
            PairValues.Add("Flag10", New String() {"Flag 10", ""})
            PairValues.Add("Flag11", New String() {"Flag 11", ""})
            PairValues.Add("Flag12", New String() {"Flag 12", ""})
            PairValues.Add("Flag13", New String() {"Flag 13", ""})
            PairValues.Add("Flag14", New String() {"Flag 14", ""})
            PairValues.Add("Flag15", New String() {"Flag 15", ""})

            PairValues.Add("TableFlag1", New String() {"Flag 1", "description"})
            PairValues.Add("TableFlag2", New String() {"Flag 2", "description"})
            PairValues.Add("TableFlag3", New String() {"Flag 3", "description"})
            PairValues.Add("TableFlag4", New String() {"Flag 4", "description"})
            PairValues.Add("TableFlag5", New String() {"Flag 5", "description"})
            PairValues.Add("TableFlag6", New String() {"Flag 6", "description"})
            PairValues.Add("TableFlag7", New String() {"Flag 7", "description"})
            PairValues.Add("TableFlag8", New String() {"Flag 8", "description"})
            PairValues.Add("TableFlag9", New String() {"Flag 9", "description"})
            PairValues.Add("TableFlag10", New String() {"Flag 10", "description"})

            PairValues.Add("FunctionCode", New String() {"Function Code", "description"})

        End Sub
    End Class
#End Region
End Namespace